# coding=UTF-8
# /*************************************************
# Copyright:
# Author:Maple_Leaves
# Date:
# Description: 这个文件是基础sql支持，注意，文件名字意思是我的sql，不是mysql
# **************************************************/
import sys
import datetime
import time


def generate_sql_file(path, filename, author):
    full_path = path + "\\" + filename +".sql"
    file_ins = open(full_path, 'w', encoding='utf-8')
    sql_info = ["-- {}","-- Author: {}",
                "-- DateCreated: {}","--------------------------------------------------------------"]

    strt = time.ctime()  # 获取标准格式字符串时间
    print(strt)

    file_ins.write(sql_info[0].format(filename))
    file_ins.write("\n")
    file_ins.write(sql_info[1].format(author))
    file_ins.write("\n")
    file_ins.write(sql_info[2].format(strt))
    file_ins.write("\n")
    file_ins.write(sql_info[3].format(author))
    file_ins.write("\n")
    file_ins.close()

    return full_path


def get_value_list_str(values_list, no_bracket=False):
    write_list = []
    write_list_str = ""
    if not no_bracket:
        write_list_str = write_list_str + "("

    value_length = len(values_list)
    for i in range(value_length):
        # print(str(type(values_list[i])))
        if str(type(values_list[i])) == "<class 'str'>":
            write_list_str = write_list_str + "\'" + values_list[i] + "\'"

        else:
            write_list_str = write_list_str + "{}".format(values_list[i])

        if i < value_length - 1:
            write_list_str = write_list_str + ","
        write_list_str = write_list_str + "\t\t"

    if not no_bracket:
        write_list_str = write_list_str + ")"
    write_list.append(write_list_str)
    # print(write_list_str)
    # print(write_list)

    # if no_bracket:
    #     write_list = write_list.split("(")[1]
    #     write_list = write_list.split(")")[0]

    return write_list


def insert_into_table_values(full_path, table_name, keys_list, values_list, extra_order="", multi_rows=True):
    file_ins = open(full_path, 'a+', encoding='utf-8')

    insert_str = "insert "
    insert_str_2 = "into {} "
    if extra_order!="":
        insert_str_full = insert_str + extra_order + insert_str_2
    else:
        insert_str_full = insert_str + insert_str_2

    file_ins.write(insert_str_full.format(table_name))
    file_ins.write("\n")

    file_ins.write("(")
    key_length = len(keys_list)
    for i in range(key_length):
        file_ins.write(keys_list[i])
        if i < key_length - 1:
            file_ins.write(",")
        file_ins.write("\t")
    file_ins.write(")")
    file_ins.write("\n")

    file_ins.write("values")
    file_ins.write("\n")
    if not multi_rows:
        write_list = get_value_list_str(values_list)
        file_ins.writelines(write_list)
        file_ins.write(";")
        file_ins.write("\n")
    else:
        row_num = len(values_list)
        for j in range(row_num):
            value_list_j = values_list[j]
            write_list = get_value_list_str(values_list[j])
            file_ins.writelines(write_list)

            if j < row_num - 1:
                file_ins.write(",")
            else:
                file_ins.write(";")
            pass
            file_ins.write("\n")
        pass

    file_ins.close()
    pass


def insert_into_table_select_where_exists(full_path, table_name, keys_list, values_list, extra_order="",
                                          table1="", key1="", value1=""):
    """
    只限单行
    insert into table (A, B)
    select
    c,d
    WHERE EXISTS (SELECT * FROM Modifiers WHERE ModifierId = 'SUK_ANGELFALLS_GOLD');
    """
    file_ins = open(full_path, 'a+', encoding='utf-8')

    insert_str = "insert "
    insert_str_2 = "into {} "
    if extra_order != "":
        insert_str_full = insert_str + extra_order + insert_str_2
    else:
        insert_str_full = insert_str + insert_str_2

    file_ins.write(insert_str_full.format(table_name))
    file_ins.write("\n")

    file_ins.write("(")
    key_length = len(keys_list)
    for i in range(key_length):
        file_ins.write(keys_list[i])
        if i < key_length - 1:
            file_ins.write(",")
        file_ins.write("\t")
    file_ins.write(")")
    file_ins.write("\n")

    file_ins.write("select")
    file_ins.write("\n")

    write_list = get_value_list_str(values_list, True)
    file_ins.writelines(write_list)
    file_ins.write("\n")

    # 新增一些判断
    where_exists_string = "WHERE EXISTS (SELECT * FROM {} WHERE {} = \'{}\')".format(table1, key1, value1)
    file_ins.write(where_exists_string)

    file_ins.write(";")
    file_ins.write("\n")

    file_ins.close()


def update_table_set_value_where_key_equals(full_path, table_name, key_set, value_set, where_key, where_value):
    file_ins = open(full_path, 'a+', encoding='utf-8')

    update_str_para_1 = "update {} set {} = {}"
    update_str_para_2 = "where {} = {};"

    if str(type(value_set)) == "<class 'str'>":
        update_str_para_1 = "update {} set {} = \'{}\'"

    elif str(type(where_value)) == "<class 'str'>":
        update_str_para_2 = "where {} = \'{}\';"

    update_str_para_full = update_str_para_1+"\n"+update_str_para_2
    update_str_full = update_str_para_full.format(table_name, key_set, value_set, where_key, where_value)

    file_ins.write(update_str_full)
    file_ins.write("\n")

    file_ins.close()
    pass


def create_table_if_not_exists(full_path, table_name, key_list, key_type_list, key_suffix_list, primary_key=""):
    file_ins = open(full_path, 'a+', encoding='utf-8')

    create_str_para_1 = "create table if not exists {} ("
    create_str_para_2 = "{} {} {},"
    create_str_para_2_v2 = "{} {} {},"
    create_str_para_3 = "primary key ({})"
    create_str_para_4 = ");"

    file_ins.write(create_str_para_1.format(table_name))
    file_ins.write("\n")

    for i in range(len(key_list)):
        file_ins.write(create_str_para_2.format(key_list[i], key_type_list[i], key_suffix_list[i]))
        file_ins.write("\n")

    if primary_key != "":
        file_ins.write(create_str_para_3.format(primary_key))
        file_ins.write("\n")

    file_ins.write(create_str_para_4)
    file_ins.write("\n")

    file_ins.close()
    pass


def delete_from_where_key_equals(full_path, table_name, where_key, where_value):
    file_ins = open(full_path, 'a+', encoding='utf-8')

    delete_str_para_1 = "delete from {}"
    delete_str_para_2 = "where {} = {};"

    if str(type(where_value)) == "<class 'str'>":
        delete_str_para_2 = "where {} = \'{}\';"

    file_ins.write(delete_str_para_1.format(table_name))
    file_ins.write("\n")
    file_ins.write(delete_str_para_2.format(where_key, where_value))
    file_ins.write("\n")

    file_ins.close()
    pass


def insert_into_select_order_by_random(full_path, table_name, key_1, key_2, value_1, dynamical_value,
                                       select_table, where_in_table, where_in_key,
                                       extra_order="", seed2=False):
    file_ins = open(full_path, 'a+', encoding='utf-8')

    isobr_str_para_1 = "insert into {}"
    if extra_order != "":
        isobr_str_para_1 = "insert " + extra_order + " into {}"
    isobr_str_para_2 = "({},\t{})"
    isobr_str_para_3 = "select"
    isobr_str_para_4 = "\'{}\',\t{}"
    isobr_str_para_5 = "from {}"
    isobr_str_para_6 = "where {} in (select {} from {})"
    isobr_str_para_7 = "order by RANDOM("
    isobr_str_para_8 = "100*STRFTIME(\'%Y%m\') + (STRFTIME(\'%d\')%3)"
    isobr_str_para_8_v2 = "100*STRFTIME(\'%Y%m\') + (STRFTIME(\'%d\')%3) + 1"
    isobr_str_para_9 = ") limit 1 ;"

    file_ins.write(isobr_str_para_1.format(table_name))
    file_ins.write("\n")
    file_ins.write(isobr_str_para_2.format(key_1, key_2))
    file_ins.write("\n")
    file_ins.write(isobr_str_para_3)
    file_ins.write("\n")
    file_ins.write(isobr_str_para_4.format(value_1, dynamical_value))
    file_ins.write("\n")
    file_ins.write(isobr_str_para_5.format(select_table))
    file_ins.write("\n")
    file_ins.write(isobr_str_para_6.format(dynamical_value, where_in_key, where_in_table))
    file_ins.write("\n")
    file_ins.write(isobr_str_para_7)
    file_ins.write("\n")
    if seed2:
        file_ins.write(isobr_str_para_8_v2)
    else:
        file_ins.write(isobr_str_para_8)
    file_ins.write("\n")
    file_ins.write(isobr_str_para_9)
    file_ins.write("\n")

    file_ins.close()

    pass


def insert_into_select_limit_offset(full_path, table_name, key_1, key_2, value_1, dynamical_value,
                                       select_table, where_in_table, where_in_key,
                                       extra_order="", seed_1=101, seed_2=101, seed_3=107):
    file_ins = open(full_path, 'a+', encoding='utf-8')

    isobr_str_para_1 = "insert into {}"
    if extra_order != "":
        isobr_str_para_1 = "insert " + extra_order + " into {}"
    isobr_str_para_2 = "({},\t{})"
    isobr_str_para_3 = "select"
    isobr_str_para_4 = "\'{}\',\t{}"
    isobr_str_para_5 = "from {}"
    isobr_str_para_6 = "where {} in (select {} from {})"
    isobr_str_para_7 = "limit 1 offset ((STRFTIME(\'%Y\')*{}+STRFTIME(\'%m\')*{}+STRFTIME(\'%d\')*{})%"

    isobr_str_para_8 = "(select COUNT(*)"

    isobr_str_para_9 = "));"

    file_ins.write(isobr_str_para_1.format(table_name))
    file_ins.write("\n")
    file_ins.write(isobr_str_para_2.format(key_1, key_2))
    file_ins.write("\n")
    file_ins.write(isobr_str_para_3)
    file_ins.write("\n")
    file_ins.write(isobr_str_para_4.format(value_1, dynamical_value))
    file_ins.write("\n")
    file_ins.write(isobr_str_para_5.format(select_table))
    file_ins.write("\n")
    file_ins.write(isobr_str_para_6.format(dynamical_value, where_in_key, where_in_table))
    file_ins.write("\n")
    file_ins.write(isobr_str_para_7.format(seed_1, seed_2, seed_3))
    file_ins.write("\n")
    file_ins.write(isobr_str_para_8)
    file_ins.write("\n")

    file_ins.write(isobr_str_para_5.format(select_table))
    file_ins.write("\n")
    file_ins.write(isobr_str_para_6.format(dynamical_value, where_in_key, where_in_table))
    file_ins.write("\n")

    file_ins.write(isobr_str_para_9)
    file_ins.write("\n")

    file_ins.close()

    pass


def insert_into_global_parameter_fcac_tables_count(full_path, fcac_table_names, extra_order=""):
    file_ins = open(full_path, 'a+', encoding='utf-8')

    isobr_str_para_1 = "insert into GlobalParameters"
    if extra_order != "":
        isobr_str_para_1 = "insert " + extra_order + " into GlobalParameters"
    isobr_str_para_2 = " (Name,\tValue)"
    isobr_str_para_3 = "values"
    isobr_str_para_4 = "(\'{}\',\t(select COUNT(*) from Traits where TraitType in (select TraitType from {})))"

    file_ins.write(isobr_str_para_1)
    file_ins.write("\n")
    file_ins.write(isobr_str_para_2)
    file_ins.write("\n")
    file_ins.write(isobr_str_para_3)
    file_ins.write("\n")

    for i in range(len(fcac_table_names)):
        table_i = fcac_table_names[i]
        global_para_name = "FCAC_PARAMETER_" + table_i

        row = isobr_str_para_4.format(global_para_name, table_i)
        if i < len(fcac_table_names) - 1:
            row = row+","
        else:
            row = row + ";"
        file_ins.write(row)
        file_ins.write("\n")

    file_ins.close()

    pass

def add_empty_rows_to_sql(full_path, emptyrow_num):
    file_ins = open(full_path, 'a+', encoding='utf-8')
    for i in range(emptyrow_num):
        file_ins.write("\n")
    file_ins.close()
    pass


if __name__ == '__main__':

    pass
